package cn.itlaobing.spring.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.itlaobing.spring.dao.BaseDao;
import cn.itlaobing.spring.dao.TeacherDao;
import cn.itlaobing.spring.entity.Teacher;

@Repository
public class TeacherDaoImpl extends BaseDao implements TeacherDao {

	@Override
	public void addTeacher(Teacher teacher) {
		// 用来获取自动增长值
		KeyHolder keyHolder = new GeneratedKeyHolder();

		String sql = "INSERT INTO teachers(NAME,gender,age) VALUES (?,?,?)";

		Object[] params = new Object[] { teacher.getName(), teacher.getGender().name(), teacher.getAge() };

		super.getJdbcTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement pstat = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
				PreparedStatementSetter pss = new ArgumentPreparedStatementSetter(params);
				pss.setValues(pstat);
				return pstat;
			}
		}, keyHolder);
		// 获取返回的自增长id
		teacher.setId(keyHolder.getKey().longValue());
	}

	@Override
	public Teacher findById(Long id) {
		try {
			String sql = "select * from teachers where id=?";
			return super.getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<>(Teacher.class), id);
		} catch (DataAccessException e) {
			return null;
		}
	}

	@Override
	public int deleteID(Long id) {
		String sql = "DELETE FROM teachers WHERE id=?";
		return super.getJdbcTemplate().update(sql, id);
	}

	@Override
	public int update(Teacher teacher) {
		String sql = "UPDATE teachers SET NAME=?,gender=?,age=? WHERE id=?";
		return super.getJdbcTemplate().update(sql, teacher.getName(), teacher.getGender().name(), teacher.getAge(),
				teacher.getId());
	}

	@Override
	public List<Teacher> findAll() {
		String sql = "select id,name,gender,age from teachers";
		return super.getJdbcTemplate().query(sql, new BeanPropertyRowMapper<>(Teacher.class));
	}

}
